﻿using System.Linq;
using System.Text;
using System.Diagnostics;
using System.Runtime.Serialization;
using System.Collections;
using System.Data;
using System.ComponentModel;
using System.Data.OracleClient;
namespace INTEX.DataBase.Tools
{
    public partial class INTEXDBC
    {
        public void MapVAFARBEN(ref DataRow dwLocal, DataRow dwData)
        {
            BlindMap(ref dwLocal, dwData);
        }
        public void SaveVAFARBEN(DataRow dwData, string ITXDBVERSION = "50.24")
        {
            DataSet setResult = new DataSet();
            DataTable tbLocal = null;
            DataRow dwLocal = null;
            bool flgExist = false;
            setResult = QueryVAFARBEN(dwData);
            if (setResult.Tables.Count > 0)
            {
                tbLocal = setResult.Tables[0];
                if (tbLocal.Rows.Count > 0)
                {
                    flgExist = true;
                    dwLocal = tbLocal.Rows[0];
                }
                else
                {
                    dwLocal = tbLocal.NewRow();
                }
            }
            MapVAFARBEN(ref dwLocal, dwData);

            if (!flgExist)
            {

                InsertVAFARBEN(dwLocal, ITXDBVERSION);
            }
            UpdateVAFARBEN(dwLocal);
        }
        public void InsertVAFARBEN(DataRow dwData, string ITXDBVERSION = "50.24")
        {
            OracleCommand or1;
            or1 = OracleDB.CreateCommand();
            switch (ITXDBVERSION)
            {
                case "50.24":
                    or1.CommandText = "INSERT INTO VAFARBEN(VAFMANDANT, VAFKOLL, VAFART, VAFFARBE, VAFLIEFERSG, VAFBEZ, VAFFFARBE, VAFDESS, VAFCOLORIT, VAFBESCHICH, VAFPKUNDNR, VAFPWERK, VAFPABT, VAFPART, VAFPVAR, VAFPLART, VAFSPERRDAT, VAFKZZOLL2, VAFFARBGRP, VAFMUFAEH, VAFCOFAEH, VAFMINPR1, VAFMINPR2, VAFABVK, VAFSFERTKZ, VAFEAN, VAFPGROE, VAFARTBEZ, VAFZUSTAND, VAFURCODE1, VAFURCODE2, VAFURCODE3, VAFURCODE4, VAFKZDUMCOL, VAFLASCHE, VAFPVARKZ, VAFSTANDARD, VAFEANLASCHE, VAFFILLER, VAFAENDZL, VAFAENDBEN, VAFAENDDAT, VAFNEUBEN, VAFNEUDAT)"
                                + "  VALUES (:VAFMANDANT, :VAFKOLL,:VAFART, :VAFFARBE, ' ', :VAFBEZ, :VAFFFARBE, '          ', '          ', '          ', '00000000', '   ', '   ', '19009.30.3          ', '11', '                    ', 0, ' ', ' ', ' ', ' ', 0, 0, ' ', ' ', '0000000000000', '       ', 'VICTORIA  ', '    ', '    ', '    ', '    ', '    ', ' ', ' ', ' ', ' ', '0000000000000', '                               ', 0, '        ', 0, 'SUNLV   ', 20110104)";
                    break;
                case "50.26":
                    or1.CommandText = "INSERT INTO VAFARBEN(VAFMANDANT, VAFKOLL, VAFART, VAFFARBE, VAFLIEFERSG, VAFBEZ, VAFFFARBE, VAFDESS, VAFCOLORIT, VAFBESCHICH, VAFPKUNDNR, VAFPWERK, VAFPABT, VAFPART, VAFPVAR, VAFPLART, VAFSPERRDAT, VAFKZZOLL2, VAFFARBGRP, VAFMUFAEH, VAFCOFAEH, VAFMINPR1, VAFMINPR2, VAFABVK, VAFSFERTKZ, VAFEAN, VAFPGROE, VAFARTBEZ, VAFZUSTAND, VAFURCODE1, VAFURCODE2, VAFURCODE3, VAFURCODE4, VAFKZDUMCOL, VAFLASCHE, VAFPVARKZ, VAFSTANDARD, VAFEANLASCHE, VAFFILLER, VAFAENDZL, VAFAENDBEN, VAFAENDDAT, VAFNEUBEN, VAFNEUDAT)"
                                + "  VALUES (:VAFMANDANT, :VAFKOLL,:VAFART, :VAFFARBE, ' ', :VAFBEZ, :VAFFFARBE, '          ', '          ', '          ', '00000000', '   ', '   ', '19009.30.3          ', '11', '                    ', 0, ' ', ' ', ' ', ' ', 0, 0, ' ', ' ', '0000000000000', '       ', 'VICTORIA  ', '    ', '    ', '    ', '    ', '    ', ' ', ' ', ' ', ' ', '0000000000000', '                               ', 0, '        ', 0, 'SUNLV   ', 20110104)";
                    break;
                default:
                    break;
                //VAFMANDANT VAFKOLL VAFART VAFFARBE VAFBEZ VAFFFARBE

            }
            or1.Parameters.Add("VAFMANDANT", OracleType.NVarChar).Value = dwData["VAFMANDANT"].ToString();
            or1.Parameters.Add("VAFKOLL", OracleType.NVarChar).Value = dwData["VAFKOLL"].ToString();
            or1.Parameters.Add("VAFART", OracleType.NVarChar).Value = dwData["VAFART"].ToString();
            or1.Parameters.Add("VAFFARBE", OracleType.NVarChar).Value = dwData["VAFFARBE"].ToString();
            or1.Parameters.Add("VAFBEZ", OracleType.NVarChar).Value = dwData["VAFBEZ"].ToString();
            or1.Parameters.Add("VAFFFARBE", OracleType.NVarChar).Value = dwData["VAFFFARBE"].ToString();
            Connect2Proxy(or1);
        }
        public DataSet QueryVAFARBEN(DataRow dwData, int option = 1)
        {
            DataSet Return;
            DataSet Result = new DataSet();
            OracleCommand or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 0:
                    or1.CommandText = "SELECT * FROM VAFARBEN WHERE  ROWNUM<2 ";
                    break;
                case 1:
                    or1.CommandText = "SELECT * FROM VAFARBEN  WHERE  VAFMANDANT=:VAFMANDANT"
                        + " AND VAFKOLL=:VAFKOLL AND VAFART =:VAFART";
                    or1.Parameters.Add("VAFMANDANT", OracleType.NVarChar).Value = dwData["VAFMANDANT"].ToString();
                    or1.Parameters.Add("VAFKOLL", OracleType.NVarChar).Value = dwData["VAFKOLL"].ToString();
                    or1.Parameters.Add("VAFART", OracleType.NVarChar).Value = dwData["VAFART"].ToString();
                    break;
                case 2:
                    or1.CommandText = "SELECT * FROM VAFARBEN  WHERE  VAFMANDANT=:VAFMANDANT"
                        + " AND VAFKOLL=:VAFKOLL AND VAFART =:VAFART AND VAFFARBE=:VAFFARBE";
                    or1.Parameters.Add("VAFMANDANT", OracleType.NVarChar).Value = dwData["VAFMANDANT"].ToString();
                    or1.Parameters.Add("VAFKOLL", OracleType.NVarChar).Value = dwData["VAFKOLL"].ToString();
                    or1.Parameters.Add("VAFART", OracleType.NVarChar).Value = dwData["VAFART"].ToString();
                    or1.Parameters.Add("VAFFARBE", OracleType.NVarChar).Value = dwData["VAFFARBE"].ToString();
                    break;
                case 3:
                    or1.CommandText = "SELECT A.* FROM VAFARBEN A JOIN VKPOS V ON A.VAFMANDANT=V.VKPMANDANT"
                        + " AND A.VAFKOLL=V.VKPKOLL AND A.VAFART=V.VKPART AND A.VAFFARBE=V.VKPFARBE"
                        + " WHERE VKPMANDANT=:VKPMANDANT AND VKPSA='  ' AND VKPAUFKRS=:VKPAUFKRS AND VKPAUFLFD=:VKPAUFLFD ";
                    or1.Parameters.Add("VKPMANDANT", OracleType.NVarChar).Value = dwData["VKPMANDANT"].ToString();
                    or1.Parameters.Add("VKPAUFKRS", OracleType.NVarChar).Value = dwData["VKPAUFKRS"].ToString();
                    or1.Parameters.Add("VKPAUFLFD", OracleType.NVarChar).Value = dwData["VKPAUFLFD"].ToString();
                    break;
                case 4:
                    or1.CommandText = "SELECT * FROM VAFARBEN  WHERE  VAFMANDANT=:VAFMANDANT"
                        + "   AND VAFART =:VAFART AND VAFFARBE=:VAFFARBE";
                    or1.Parameters.Add("VAFMANDANT", OracleType.NVarChar).Value = dwData["VAFMANDANT"].ToString();
                    or1.Parameters.Add("VAFART", OracleType.NVarChar).Value = dwData["VAFART"].ToString();
                    or1.Parameters.Add("VAFFARBE", OracleType.NVarChar).Value = dwData["VAFFARBE"].ToString();
                    break;
                default:
                    break;
            }
            Result = Connect2Proxy(or1);
            if (Result.Tables.Count > 0)
                Result.Tables[0].TableName = "VAFARBEN";
            Return = Result;
            return Return;
        }
        public DataSet QueryVAFARBEN(string client, string coll, string art, string color)
        {
            DataSet Return;
            DataSet Result = new DataSet();
            OracleCommand or1 = OracleDB.CreateCommand();
            or1.CommandText = "SELECT * FROM VAFARBEN  WHERE VAFMANDANT=:VAFMANDANT"
            + " AND VAFKOLL=:VAFKOLL AND VAFART =:VAFART AND VAFFARBE=:VAFFARBE"
            ;
            or1.Parameters.Add("VAFMANDANT", OracleType.NVarChar).Value = client;
            or1.Parameters.Add("VAFKOLL", OracleType.NVarChar).Value = coll;
            or1.Parameters.Add("VAFART", OracleType.NVarChar).Value = art;
            or1.Parameters.Add("VAFFARBE", OracleType.NVarChar).Value = color;
            Result = Connect2Proxy(or1);
            if (Result.Tables.Count > 0)
                Result.Tables[0].TableName = "VAFARBEN";
            Return = Result;
            return Return;
        }
        public DataSet QueryVAFARBEN(DataRow dwData)
        {
            DataSet Return;
            DataSet Result = new DataSet();
            OracleCommand or1 = OracleDB.CreateCommand();
            or1.CommandText = "SELECT * FROM VAFARBEN  WHERE VAFMANDANT=:VAFMANDANT"
            + " AND VAFKOLL=:VAFKOLL AND VAFART =:VAFART AND VAFFARBE=:VAFFARBE"
            ;
            or1.Parameters.Add("VAFMANDANT", OracleType.NVarChar).Value = dwData["VAFMANDANT"].ToString();
            or1.Parameters.Add("VAFKOLL", OracleType.NVarChar).Value = dwData["VAFKOLL"].ToString();
            or1.Parameters.Add("VAFART", OracleType.NVarChar).Value = dwData["VAFART"].ToString();
            or1.Parameters.Add("VAFFARBE", OracleType.NVarChar).Value = dwData["VAFFARBE"].ToString();
            Result = Connect2Proxy(or1);
            if (Result.Tables.Count > 0)
                Result.Tables[0].TableName = "VAFARBEN";
            Return = Result;
            return Return;
        }
        public void UpdateVAFARBEN(DataRow dwData, string ITXDBVERSION = "50.24", int option = 1)
        {
            OracleCommand or1;
            DataSet Result = new DataSet();
            or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 1:
                    #region====Command====

                    or1.CommandText = "UPDATE VAFARBEN SET "
                        + "VAFMANDANT=:VAFMANDANT,"
                        + "VAFKOLL=:VAFKOLL,"
                        + "VAFART=:VAFART,"
                        + "VAFFARBE=:VAFFARBE,"
                        + "VAFLIEFERSG=:VAFLIEFERSG,"
                        + "VAFBEZ=:VAFBEZ,"
                        + "VAFFFARBE=:VAFFFARBE,"
                        + "VAFDESS=:VAFDESS,"
                        + "VAFCOLORIT=:VAFCOLORIT,"
                        + "VAFBESCHICH=:VAFBESCHICH,"
                        + "VAFPKUNDNR=:VAFPKUNDNR,"
                        + "VAFPWERK=:VAFPWERK,"
                        + "VAFPABT=:VAFPABT,"
                        + "VAFPART=:VAFPART,"
                        + "VAFPVAR=:VAFPVAR,"
                        + "VAFPLART=:VAFPLART,"
                        + "VAFSPERRDAT=:VAFSPERRDAT,"
                        + "VAFKZZOLL2=:VAFKZZOLL2,"
                        + "VAFFARBGRP=:VAFFARBGRP,"
                        + "VAFMUFAEH=:VAFMUFAEH,"
                        + "VAFCOFAEH=:VAFCOFAEH,"
                        + "VAFMINPR1=:VAFMINPR1,"
                        + "VAFMINPR2=:VAFMINPR2,"
                        + "VAFABVK=:VAFABVK,"
                        + "VAFSFERTKZ=:VAFSFERTKZ,"
                        + "VAFEAN=:VAFEAN,"
                        + "VAFPGROE=:VAFPGROE,"
                        + "VAFARTBEZ=:VAFARTBEZ,"
                        + "VAFZUSTAND=:VAFZUSTAND,"
                        + "VAFURCODE1=:VAFURCODE1,"
                        + "VAFURCODE2=:VAFURCODE2,"
                        + "VAFURCODE3=:VAFURCODE3,"
                        + "VAFURCODE4=:VAFURCODE4,"
                        + "VAFKZDUMCOL=:VAFKZDUMCOL,"
                        + "VAFLASCHE=:VAFLASCHE,"
                        + "VAFPVARKZ=:VAFPVARKZ,"
                        + "VAFSTANDARD=:VAFSTANDARD,"
                        + "VAFEANLASCHE=:VAFEANLASCHE,"
                        + "VAFFILLER=:VAFFILLER,"
                        + "VAFAENDZL=:VAFAENDZL,"
                        + "VAFAENDBEN=:VAFAENDBEN,"
                        + "VAFAENDDAT=:VAFAENDDAT,"
                        + "VAFNEUBEN=:VAFNEUBEN,"
                        + "VAFNEUDAT=:VAFNEUDAT"
                        + " WHERE  "
                        + "  VAFMANDANT=:VAFMANDANT AND VAFKOLL=:VAFKOLL AND VAFART=:VAFART AND VAFFARBE=:VAFFARBE";
                    #endregion

                    #region====Parameters====
                    or1.Parameters.Add("VAFMANDANT", OracleType.NVarChar).Value = dwData["VAFMANDANT"].ToString();
                    or1.Parameters.Add("VAFKOLL", OracleType.NVarChar).Value = dwData["VAFKOLL"].ToString();
                    or1.Parameters.Add("VAFART", OracleType.NVarChar).Value = dwData["VAFART"].ToString();
                    or1.Parameters.Add("VAFFARBE", OracleType.NVarChar).Value = dwData["VAFFARBE"].ToString();
                    or1.Parameters.Add("VAFLIEFERSG", OracleType.NVarChar).Value = dwData["VAFLIEFERSG"].ToString();
                    or1.Parameters.Add("VAFBEZ", OracleType.NVarChar).Value = dwData["VAFBEZ"].ToString();
                    or1.Parameters.Add("VAFFFARBE", OracleType.NVarChar).Value = dwData["VAFFFARBE"].ToString();
                    or1.Parameters.Add("VAFDESS", OracleType.NVarChar).Value = dwData["VAFDESS"].ToString();
                    or1.Parameters.Add("VAFCOLORIT", OracleType.NVarChar).Value = dwData["VAFCOLORIT"].ToString();
                    or1.Parameters.Add("VAFBESCHICH", OracleType.NVarChar).Value = dwData["VAFBESCHICH"].ToString();
                    or1.Parameters.Add("VAFPKUNDNR", OracleType.NVarChar).Value = dwData["VAFPKUNDNR"].ToString();
                    or1.Parameters.Add("VAFPWERK", OracleType.NVarChar).Value = dwData["VAFPWERK"].ToString();
                    or1.Parameters.Add("VAFPABT", OracleType.NVarChar).Value = dwData["VAFPABT"].ToString();
                    or1.Parameters.Add("VAFPART", OracleType.NVarChar).Value = dwData["VAFPART"].ToString();
                    or1.Parameters.Add("VAFPVAR", OracleType.NVarChar).Value = dwData["VAFPVAR"].ToString();
                    or1.Parameters.Add("VAFPLART", OracleType.NVarChar).Value = dwData["VAFPLART"].ToString();
                    or1.Parameters.Add("VAFSPERRDAT", OracleType.NVarChar).Value = dwData["VAFSPERRDAT"].ToString();
                    or1.Parameters.Add("VAFKZZOLL2", OracleType.NVarChar).Value = dwData["VAFKZZOLL2"].ToString();
                    or1.Parameters.Add("VAFFARBGRP", OracleType.NVarChar).Value = dwData["VAFFARBGRP"].ToString();
                    or1.Parameters.Add("VAFMUFAEH", OracleType.NVarChar).Value = dwData["VAFMUFAEH"].ToString();
                    or1.Parameters.Add("VAFCOFAEH", OracleType.NVarChar).Value = dwData["VAFCOFAEH"].ToString();
                    or1.Parameters.Add("VAFMINPR1", OracleType.NVarChar).Value = dwData["VAFMINPR1"].ToString();
                    or1.Parameters.Add("VAFMINPR2", OracleType.NVarChar).Value = dwData["VAFMINPR2"].ToString();
                    or1.Parameters.Add("VAFABVK", OracleType.NVarChar).Value = dwData["VAFABVK"].ToString();
                    or1.Parameters.Add("VAFSFERTKZ", OracleType.NVarChar).Value = dwData["VAFSFERTKZ"].ToString();
                    or1.Parameters.Add("VAFEAN", OracleType.NVarChar).Value = dwData["VAFEAN"].ToString();
                    or1.Parameters.Add("VAFPGROE", OracleType.NVarChar).Value = dwData["VAFPGROE"].ToString();
                    or1.Parameters.Add("VAFARTBEZ", OracleType.NVarChar).Value = dwData["VAFARTBEZ"].ToString();
                    or1.Parameters.Add("VAFZUSTAND", OracleType.NVarChar).Value = dwData["VAFZUSTAND"].ToString();
                    or1.Parameters.Add("VAFURCODE1", OracleType.NVarChar).Value = dwData["VAFURCODE1"].ToString();
                    or1.Parameters.Add("VAFURCODE2", OracleType.NVarChar).Value = dwData["VAFURCODE2"].ToString();
                    or1.Parameters.Add("VAFURCODE3", OracleType.NVarChar).Value = dwData["VAFURCODE3"].ToString();
                    or1.Parameters.Add("VAFURCODE4", OracleType.NVarChar).Value = dwData["VAFURCODE4"].ToString();
                    or1.Parameters.Add("VAFKZDUMCOL", OracleType.NVarChar).Value = dwData["VAFKZDUMCOL"].ToString();
                    or1.Parameters.Add("VAFLASCHE", OracleType.NVarChar).Value = dwData["VAFLASCHE"].ToString();
                    or1.Parameters.Add("VAFPVARKZ", OracleType.NVarChar).Value = dwData["VAFPVARKZ"].ToString();
                    or1.Parameters.Add("VAFSTANDARD", OracleType.NVarChar).Value = dwData["VAFSTANDARD"].ToString();
                    or1.Parameters.Add("VAFEANLASCHE", OracleType.NVarChar).Value = dwData["VAFEANLASCHE"].ToString();
                    or1.Parameters.Add("VAFFILLER", OracleType.NVarChar).Value = dwData["VAFFILLER"].ToString();
                    or1.Parameters.Add("VAFAENDZL", OracleType.NVarChar).Value = dwData["VAFAENDZL"].ToString();
                    or1.Parameters.Add("VAFAENDBEN", OracleType.NVarChar).Value = dwData["VAFAENDBEN"].ToString();
                    or1.Parameters.Add("VAFAENDDAT", OracleType.NVarChar).Value = dwData["VAFAENDDAT"].ToString();
                    or1.Parameters.Add("VAFNEUBEN", OracleType.NVarChar).Value = dwData["VAFNEUBEN"].ToString();
                    or1.Parameters.Add("VAFNEUDAT", OracleType.NVarChar).Value = dwData["VAFNEUDAT"].ToString();
                    #endregion
                    break;
                case 2:
                    #region====Parameters====
                    or1.CommandText = "UPDATE VAFARBEN SET "
                + "VAFARTBEZ=:VAFARTBEZ"
                + " WHERE  "
                + "  VAFMANDANT=:VAFMANDANT  AND VAFART=:VAFART  ";
                    or1.Parameters.Add("VAFMANDANT", OracleType.NVarChar).Value = dwData["VAFMANDANT"].ToString();
                    or1.Parameters.Add("VAFART", OracleType.NVarChar).Value = dwData["VAFART"].ToString();
                    or1.Parameters.Add("VAFARTBEZ", OracleType.NVarChar).Value = dwData["VAFARTBEZ"].ToString();
                    #endregion
                    break;
                default:
                    break;
            }


            Connect2Proxy(or1);
        }
    }
}
